AppFlow+Snowflake+Tableauで可視化する！クラメソ面白Slackチャンネル決定戦
儲かってまっか？データアナリティクス事業本部プロダクト営業部の営業じゃない方・新納や。
弊社クラスメソッドのslackでは、面白いメッセージに「草」というリアクションが付けられることがあります。
そしてこの「草リアクション」を付けられたメッセージは、Reacji-Channelerという機能を使ってnotify-kusaというチャンネルにリダイレクトされるようになっています。
幼少期にベイブレード・遊戯王・ポケモンを楽しみ、ホビーカルチャーが骨の髄まで染み込んだ平成キッズの私としては、常に闘争心が脳を支配し、大人になった今でも「戦って勝ちたい」「誰が一番（てっぺん）か決めたい」という思いを抑えきれません。
ということで、どのSlackチャンネルからのメッセージが一番notify-kusaチャンネルにリダイレクトされているのか、AppFlow+Snowflake+Tableauを使って可視化してみました。
前提条件
今回の面白Slackチャンネル決定戦を実現する環境の構成図は以下の通り。
以下のステップでデータ取得～可視化まで実施します。
- Amazon AppFlowでSlackのデータをS3に配置する
- S3に配置されたJSONデータをSnowflakeに取り込み、可視化用のテーブルを作成する
- Snowflake上のデータをTableau Desktopで可視化する
使用した環境は以下の通り。
- Snowflake 4.18.1
- Tableau Desktop 2020.2
なお、「草」リアクションを1回付けられた時点で即notify-kusaチャンネルにリダイレクトされるため、同じ投稿に草を付けられた回数が多くてもカウントは1です。つまり、たくさん投稿して一度でも「草」リアクションが付けられる方が有利です。
SlackのデータをAmazon AppFlowを使ってS3に配置する
まずはSlackチャンネルに投稿されたメッセージを取得してS3に配置します。フルマネージドなデータ連携サービスであるAmazon AppFlowを使用することでノンコーディングでのSlack→S3の連携が可能です。詳細な使用方法は以下エントリをご確認ください。
AppFlowはデータ連携先としてSnowflakeをサポートしていますが、以下エントリにもある通り2020年5月時点でus-east-2.aws、ca-central-1.aws、ap-northeast-1.aws以外のSnowflakeリージョンを選択する必要があるため、一旦S3にデータを保存してからSnowflakeで取り込むという形をとっています。
SnowflakeでS3上のJSONデータを取得し、可視化用テーブルを作成する
「SlackのデータってJSONやん…可視化用に整形するの面倒やな…」と思っていたのですが、SnowflakeはJSONのような半構造化データをサポートしています。なんとJSON形式のデータをドーンと入れておくだけで簡単にクエリできるようになるんです！
データベースを作成していない場合は「データベース」タブ→「作成」からデータベースを作成しておきます。
テーブルを作成する
ワークシートより以下SQL文を実行し、SlackのJSONデータを格納しておくためのテーブルを作成しましょう。半構造化データをいれるにはカラムのデータ型をVARIANT型にしておきます。
create table json_notify_kusa (v variant);
ステージを設定する
SlackのJSONデータを格納しているS3バケットを外部テーブルに指定します。「データベース」タブ→「ステージ」タブ→作成からステージを作成します。
ステージングするファイルの場所にS3を選択します。
必要情報を以下の通り入力します。
| 項目 | 設定値 |
|---|---|
| 名前 | 任意のステージ名 |
| URL | S3パス |
| AWSキーID | S3へのアクセス権を持つIAMユーザーのアクセスキーID |
| AWS秘密キー | S3へのアクセス権を持つIAMユーザーのシークレットキー |
S3上のデータをテーブルにロードする
上記で設定したステージのデータをテーブルにロードします。file_formatには(type=json)を、from句には@ステージ名を指定します。
copy into json_notify_kusa from @cm_niino_s3 file_format = (type=json);
select文を発行するとJSON形式でデータが格納されていることが確認できます。
JSONを可視化用に構造化する
この一連の検証で一番感動したポイントです。無事にデータをロードできたものの、JSON形式のままではTableauでの可視化ができません。データ加工ツールに接続したり整形のためのプログラムを書いたりしなくても、クエリを書くだけでJSON形式のデータを集計に必要な項目のみ抜き出して簡単に構造化することができるんです。そう、Snowflakeならね。
一例ですが、テーブルに格納されているSlackデータは以下の通りです。
{
"attachments": [
{
"from_url": "https://hogehoge",
"fallback": "hogehoge",
"ts": "1590130941.131400",
"author_id": "HOGEHOGE",
"author_subname": "ユーザー名",
"channel_id": "チャンネルID",
"channel_name": "チャンネル名",
"is_msg_unfurl": true,
"text": "新納さんはフリー素材なんですね！ありがとうございます。参考にして考えてみます。",
"author_name": "ユーザー名",
"author_link": "https://hogehoge",
"author_icon": "https://hogehoge",
"mrkdwn_in": [
"text"
],
"id": 1,
"original_url": "https://hogehoge",
"footer": "Posted in #チャンネル名"
}
],
"bot_id": "HOGEHOGE",
"subtype": "bot_message",
"text": "<https://hogehoge>",
"ts": "1590130959.072400",
"type": "message"
}
チャンネル名、ユーザーID、ユーザー名を取り出してビューを作成してみましょう。select文には<カラム名>:<配列の第1要素>[0].<子要素>::<データ型>を指定します。
create view json_notify_kusa_view as select v:attachments[0].channel_name::string as channnel_name, v:attachments[0].author_name::string as author_name, v:attachments[0].author_id::string as author_id from json_notify_kusa;
実行すると構造化されたデータでビューが作られました。感動の瞬間です。
半構造化データに対してどのようなクエリが実行できるのかについては、以下のヘルプをご参照ください。
Tableau Desktopで面白チャンネルを可視化する
ここまで準備できればTableauで簡単に可視化できます。SnowflakeをTableau Desktopに接続する方法については以下エントリをご覧ください。
Slackのチャンネル名を行に、草チャンネルに出現したカウントを列に配置します。
このままだと表示されるチャンネル数が多くて見づらいので上位10位までを表示させてみましょう。行に指定したチャンネル名を右クリック→作成→セットを選びます。
上位タブのフィールド指定を選択し、チャンネル名のカウント上位10件のみを表示させる設定をします。
作成したセットをフィルターにドラッグアンドドロップで配置すると面白チャンネル上位10件に絞られました。
結果発表
せっかくなので上位3つのチャンネルを紹介します。
第3位：個人分報チャンネル「times-niino-kaori」
今やっているタスクや困っていることを呟いておく、いわば仕事版Twitterのような「分報」と呼ばれる文化が弊社Slackにあります。私、新納個人の分報チャンネルが3位でした。
私の知らない間に日次バッチで投稿が収集されていたり、勝手にComprehendで投稿を感情分析されたりと、他のメンバーからサンドボックスか何かと思われているようなチャンネルのランクインでした。どうして…
（分報について知りたい方は以下リンクをご参照ください）
第2位：大阪オフィス雑談チャンネル「misc-osaka」
お笑い刺客が多数在籍する、弊社大阪オフィス所属メンバー向け雑談チャンネルが2位でした。
何故か実際の大阪オフィス所属メンバーの3倍近い人数が参加しており、社内雑談系チャンネルの中でも随一の投稿数を誇ります。メンバーが仕事に集中してしまい、チャンネルの投稿が少なくて寂しいときも「今日はこの部屋静かだな」と書き込むだけでサンシャイン池崎bot（多言語対応済み）が返事を返してくれる、温かいチャンネルです。
漫画『刃牙』に出てくる最強死刑囚の人気投票が何の脈絡もなく行われても受け入れられる…そんな懐の広さもランクインの一因でしょう。
第1位：全社共通雑談チャンネル「misc」
雑談系チャンネルの中でも参加人数トップ、全社共通雑談チャンネルが堂々の第1位でした。
社内LT大会のみならず、社長や部長陣の発表する全社員向け報告会の実況も行われる賑やかなチャンネルです。過去には社長の発表が長引くかどうかの予想スレが立てられたことも。
このチャンネルに投稿された一言がきっかけとなり、最終的には弊社カフェ『Developers.IO CAFE』でブルボン総選挙が行われるまで至ったこともありました。インスピレーションの源泉のような存在であるところもトップの秘訣なのかもしれません。
おまけ：一番「草」リアクションを付けられているユーザーは誰だ？
取得データの中にはユーザー名も入っているので、もちろん最も「草」リアクションを付けられているユーザーも確認可能です。
author_idごとにカウントをとればいいのですが、IDにユーザー名を引き当てようとすると、ユーザー名を途中で変更した等の原因でユニークなIDに対しユーザー名が複数存在するケースがあります。
今回はSnowflake上にIDとユーザー名が一意となるマスタとしてビューを作成しました。
create view Author_Name_master as
select distinct author_id,
first_value(author_name) over (partition by author_id order by author_id) as author_name
from notify_kusa order by author_id;
Tableau Desktopに接続し、ドラッグアンドドロップでマスタテーブルと集計に用いるテーブルを接続します。author_idで結合するようにリレーションシップの編集をします。
シートに移動し、マスタのauthor_name（ユーザー名）ピルを行に配置し、適宜上位10位までを表示するセットを作れば面白ユーザーランキングの完成です。さてさて、面白芸人は誰かな～？
私でした…。
まとめ
AppFlowでSlackデータを取得し、SnowflakeでJSONデータを構造化し、Tableauで可視化する方法をご紹介しました。
「社内で一番面白いチャンネルはどこだろう？」と疑問に思ったら簡単に時間をかけず結果を得られたのは、ノンコーディングでELT～可視化までできることの強みです。特にSlackのデータをAppFlowで簡単に取得し、JSONのパースがSnowflake上でサクッとできたのはかなりの時間短縮になりました。時間短縮ができるということは、可視化されたデータに基づいた意思決定やその先のフェーズにいち早くたどり着き、試行錯誤に時間を当てられるということでもあります。今回の面白チャンネル可視化結果によって、クラスメソッド社員の誰かが素敵なチャンネルに巡り合い、面白トークが活発になるといったお役に立てれば幸いです。（それくらいしか役に立たない可視化結果）
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
